﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace LimpaGaragem.DAO.User
{
    public class Info
    {
        /// <summary>
        /// Get user
        /// </summary>
        public LimpaGaragem.Model.User.Info GetUser(int IDUser)
        {
            try
            {
                Framework.DataAccess objDataAccess = new Framework.DataAccess();

                //PARAMETERS
                List<SqlParameter> objListOfParameters = new List<SqlParameter>();
                objListOfParameters.Add(new SqlParameter("@ID_USER", IDUser));

                //EXECUTE
                DataTable objDataTable = objDataAccess.ReturnDataTable(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, Framework.DataAccess.QueryType.Procedure, "PR001_SelectUser", 0, objListOfParameters);
                LimpaGaragem.Model.User.Info objInfo = null;
                if (objDataTable.Rows.Count > 0)
                {
                    objInfo = new LimpaGaragem.Model.User.Info();
                    objInfo.ID = IDUser;
                    objInfo.Login = objDataTable.Rows[0]["LG_USER"].ToString();
                    objInfo.Name = objDataTable.Rows[0]["NM_USER"].ToString();
                    objInfo.Email = objDataTable.Rows[0]["DS_EMAIL"].ToString();
                    objInfo.DataBirthday = Convert.ToDateTime(objDataTable.Rows[0]["DT_BIRTHDAY"]);
                    objInfo.CPF = Convert.ToDecimal(objDataTable.Rows[0]["NU_CPF"]);
                    if(objDataTable.Rows[0]["DS_ADRESS"] != DBNull.Value)
                        objInfo.Adress = objDataTable.Rows[0]["DS_ADRESS"].ToString();
                    if (objDataTable.Rows[0]["NU_ADRESS"] != DBNull.Value)
                        objInfo.AdressNumber = Convert.ToInt32(objDataTable.Rows[0]["NU_ADRESS"]);
                    if (objDataTable.Rows[0]["DS_ADDITIONAL_ADRESS"] != DBNull.Value)
                        objInfo.AdditionalAdress = objDataTable.Rows[0]["DS_ADDITIONAL_ADRESS"].ToString();
                    if (objDataTable.Rows[0]["NU_CEP"] != DBNull.Value)
                        objInfo.CEP = Convert.ToDecimal(objDataTable.Rows[0]["NU_CEP"]);
                    if (objDataTable.Rows[0]["ID_CITY"] != DBNull.Value)
                        objInfo.IDCity = Convert.ToInt32(objDataTable.Rows[0]["ID_CITY"]);
                    if (objDataTable.Rows[0]["NU_PHONE1"] != DBNull.Value)
                        objInfo.Phone1 = objDataTable.Rows[0]["NU_PHONE1"].ToString();
                    if (objDataTable.Rows[0]["NU_PHONE2"] != DBNull.Value)
                        objInfo.Phone2 = objDataTable.Rows[0]["NU_PHONE2"].ToString();
                    objInfo.ShowContactInfo = Convert.ToBoolean(objDataTable.Rows[0]["IC_SHOW_CONTACT_INFO"]);
                    objInfo.IDStatus = Convert.ToInt32(objDataTable.Rows[0]["ID_STATUS"]);
                    objInfo.DateTimeInserted = Convert.ToDateTime(objDataTable.Rows[0]["DT_INC"]);
                    if (objDataTable.Rows[0]["DT_UPD"] != DBNull.Value)
                        objInfo.DateTimeInserted = Convert.ToDateTime(objDataTable.Rows[0]["DT_UPD"]);
                }
                return objInfo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Insert new user
        /// </summary>
        /// <returns>ID USER</returns>
        public int InsertUser(  string Login,
                                string Name,
                                string Email,
                                string Password,
                                DateTime DataBirthday,
                                decimal CPF,
                                string Adress,
                                int? AdressNumber,
                                string AdditionalAdress,
                                decimal? CEP,
                                int? IDCity,
                                string Phone1,
                                string Phone2,
                                bool ShowContactInfo)
        {
            try
            {
                Framework.DataAccess objDataAccess = new Framework.DataAccess();

                //PARAMETERS
                List<SqlParameter> objListOfParameters = new List<SqlParameter>();
                objListOfParameters.Add(new SqlParameter("@LG_USER", Login));
                objListOfParameters.Add(new SqlParameter("@NM_USER", Name));
                objListOfParameters.Add(new SqlParameter("@DS_EMAIL", Email));
                objListOfParameters.Add(new SqlParameter("@DS_PASSWORD", Password));
                objListOfParameters.Add(new SqlParameter("@DT_BIRTHDAY", DataBirthday));
                objListOfParameters.Add(new SqlParameter("@NU_CPF", CPF));
                if(Adress.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@DS_ADRESS", Adress));
                else
                    objListOfParameters.Add(new SqlParameter("@DS_ADRESS", DBNull.Value));
                if (AdressNumber.HasValue)
                    objListOfParameters.Add(new SqlParameter("@NU_ADRESS", Adress));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_ADRESS", DBNull.Value));
                if (AdditionalAdress.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@DS_ADDITIONAL_ADRESS", AdditionalAdress));
                else
                    objListOfParameters.Add(new SqlParameter("@DS_ADDITIONAL_ADRESS", DBNull.Value));
                if (CEP.HasValue)
                    objListOfParameters.Add(new SqlParameter("@NU_CEP", Adress));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_CEP", DBNull.Value));
                if (IDCity.HasValue)
                    objListOfParameters.Add(new SqlParameter("@ID_CITY", IDCity));
                else
                    objListOfParameters.Add(new SqlParameter("@ID_CITY", DBNull.Value));
                if (Phone1.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE1", Phone1));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE1", DBNull.Value));
                if (Phone2.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE2", Phone1));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE2", DBNull.Value));
                objListOfParameters.Add(new SqlParameter("@IC_SHOW_CONTACT_INFO", ShowContactInfo));

                //EXECUTE
                return objDataAccess.ExecuteQueryAndReturnValue(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, Framework.DataAccess.QueryType.Procedure, "PR001_InsertUser", 0, objListOfParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Send email to confirm register
        /// </summary>
        public void SendConfirmationEmail(string RecipientMail,int IDUser,string UserLogin, string UserPassword, string UserName)
        {
            try
            {
                LimpaGaragem.Framework.Mail objMail = new Framework.Mail();

                //FORMAT HTML
                string html = string.Empty;
                html += "<html><body>";
                html += "<table style='color:#000;font-family:arial;' align='center'>";
                html += "<tr><td style='font-size:14px'>Confirmação de Cadastro</td></tr>";
                html += "<tr><td style='font-size:10px'><br>Olá <b>##NAME##</b></td></tr>";
                html += "<tr><td style='font-size:10px'><br>Seu cadastro foi realizado, para dar prosseguimento na ativação de sua conta de usuário clique no link abaixo:</td></tr>";
                html += "<tr><td style='font-size:10px'><br>Usuário: <b>##USER##</b></td></tr>";
                html += "<tr><td style='font-size:10px'><br>Senha: <b>##PASSWORD##</b></td></tr>";
                html += "<tr><td style='font-size:10px'><br>Link de confirmação: <a href='##LINK##'><b>##LINK##</b></a></td></tr>";
                html += "</table></body></html>";

                //REPLACE VARIABLES
                html = html.Replace("##NAME##", UserName);
                html = html.Replace("##USER##", UserLogin);
                html = html.Replace("##PASSWORD##", UserPassword);

                string key = LimpaGaragem.Framework.Encryption.Encrypt(IDUser.ToString()+";"+UserLogin+";"+UserPassword);
                string strLink = ConfigurationManager.AppSettings["Domain"].ToString() + "/Pages/Users/ConfirmRegister.aspx?authkey=" + key;
                html = html.Replace("##LINK##", strLink);


                //SEND EMAIL
                objMail.SendMail(ConfigurationManager.AppSettings["SenderRegisterEmail"].ToString(),
                                RecipientMail,
                                "",
                                "",
                                "Confirmação de Cadastro",
                                html,
                                1,
                                1);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Validate confirm register of user
        /// </summary>
        public bool ValidateConfirmRegister(string key)
        {
            try
            {
                string strDecryptKey = LimpaGaragem.Framework.Encryption.Decrypt(key);
                string[] values = strDecryptKey.Split(';');
                string IDUser = values.GetValue(0).ToString();
                string UserLogin = values.GetValue(1).ToString();
                string UserPassword = values.GetValue(2).ToString();
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Alter user
        /// </summary>
        public void AlterUser(int IDUser,
                                string Login,
                                string Name,
                                string Email,
                                string Password,
                                DateTime DataBirthday,
                                decimal CPF,
                                string Adress,
                                int? AdressNumber,
                                string AdditionalAdress,
                                decimal? CEP,
                                int? IDCity,
                                string Phone1,
                                string Phone2,
                                bool ShowContactInfo)
        {
            try
            {
                Framework.DataAccess objDataAccess = new Framework.DataAccess();

                //PARAMETERS
                List<SqlParameter> objListOfParameters = new List<SqlParameter>();
                objListOfParameters.Add(new SqlParameter("@ID_USER", IDUser));
                objListOfParameters.Add(new SqlParameter("@LG_USER", Login));
                objListOfParameters.Add(new SqlParameter("@NM_USER", Name));
                objListOfParameters.Add(new SqlParameter("@DS_EMAIL", Email));
                objListOfParameters.Add(new SqlParameter("@DS_PASSWORD", Password));
                objListOfParameters.Add(new SqlParameter("@DT_BIRTHDAY", DataBirthday));
                objListOfParameters.Add(new SqlParameter("@NU_CPF", CPF));
                if (Adress.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@DS_ADRESS", Adress));
                else
                    objListOfParameters.Add(new SqlParameter("@DS_ADRESS", DBNull.Value));
                if (AdressNumber.HasValue)
                    objListOfParameters.Add(new SqlParameter("@NU_ADRESS", AdressNumber));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_ADRESS", DBNull.Value));
                if (AdditionalAdress.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@DS_ADDITIONAL_ADRESS", AdditionalAdress));
                else
                    objListOfParameters.Add(new SqlParameter("@DS_ADDITIONAL_ADRESS", DBNull.Value));
                if (CEP.HasValue)
                    objListOfParameters.Add(new SqlParameter("@NU_CEP", Adress));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_CEP", DBNull.Value));
                if (IDCity.HasValue)
                    objListOfParameters.Add(new SqlParameter("@ID_CITY", IDCity));
                else
                    objListOfParameters.Add(new SqlParameter("@ID_CITY", DBNull.Value));
                if (Phone1.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE1", Phone1));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE1", DBNull.Value));
                if (Phone2.Trim() != string.Empty)
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE2", Phone1));
                else
                    objListOfParameters.Add(new SqlParameter("@NU_PHONE2", DBNull.Value));
                objListOfParameters.Add(new SqlParameter("@IC_SHOW_CONTACT_INFO", ShowContactInfo));

                //EXECUTE
                objDataAccess.ExecuteQuery(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, Framework.DataAccess.QueryType.Procedure, "PR001_AlterUser", 0, objListOfParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Delete user
        /// </summary>
        public void DeleteUser(int IDUser)
        {
            try
            {
                Framework.DataAccess objDataAccess = new Framework.DataAccess();

                //PARAMETERS
                List<SqlParameter> objListOfParameters = new List<SqlParameter>();
                objListOfParameters.Add(new SqlParameter("@ID_USER", IDUser));

                //EXECUTE
                objDataAccess.ExecuteQuery(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, Framework.DataAccess.QueryType.Procedure, "PR001_DeleteUser", 0, objListOfParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Alter password of user
        /// </summary>
        public void AlterUserPassword(int IDUser,string Password)
        {
            try
            {
                Framework.DataAccess objDataAccess = new Framework.DataAccess();

                //PARAMETERS
                List<SqlParameter> objListOfParameters = new List<SqlParameter>();
                objListOfParameters.Add(new SqlParameter("@ID_USER", IDUser));
                objListOfParameters.Add(new SqlParameter("@DS_PASSWORD", Password));

                //EXECUTE
                objDataAccess.ExecuteQuery(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, Framework.DataAccess.QueryType.Procedure, "PR001_AlterUserPassword", 0, objListOfParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Alter status of user
        /// </summary>
        public void AlterUserStatus(int IDUser,int IDStatus)
        {
            try
            {
                Framework.DataAccess objDataAccess = new Framework.DataAccess();

                //PARAMETERS
                List<SqlParameter> objListOfParameters = new List<SqlParameter>();
                objListOfParameters.Add(new SqlParameter("@ID_USER", IDUser));
                objListOfParameters.Add(new SqlParameter("@ID_STATUS", IDStatus));

                //EXECUTE
                objDataAccess.ExecuteQuery(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, Framework.DataAccess.QueryType.Procedure, "PR001_AlterUserStatus", 0, objListOfParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
